﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class MobileDAL
    {

        #region[Mobile_SelectALL]
        public List<DTO.MobileDTO> Mobile_SelectALL()
        {
            List<DTO.MobileDTO> mball = new List<DTO.MobileDTO>();
            
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectAllMobile", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.MobileDTO mb = new DTO.MobileDTO();
                mb.MobileID = dr.GetInt32(0);
                mb.Name = dr.GetString(1);
                mb.Brand = dr.GetString(2);
                mb.Description = dr.GetString(3);
                mb.Prize = dr.GetInt32(4);
                mb.Color = dr.GetString(5);
                mb.WarrantyTime = dr.GetInt32(6);
                mb.State = dr.GetString(7);
                mb.ImageID = dr.GetInt32(8);
                mb.Count = dr.GetInt32(9);
                mball.Add(mb);
            }
            dr.Close();
            con.Close();

            return mball;
        }
        #endregion

        #region[Mobile_SelectByName]
        public List<DTO.MobileDTO> Mobile_SelectByName(String name)
        {
            List<DTO.MobileDTO> mbname = new List<DTO.MobileDTO>();
            
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectMobileByName", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@_name", name);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.MobileDTO mb = new DTO.MobileDTO();
                mb.MobileID = dr.GetInt32(0);
                mb.Name = dr.GetString(1);
                mb.Brand = dr.GetString(2);
                mb.Description = dr.GetString(3);
                mb.Prize = dr.GetInt32(4);
                mb.Color = dr.GetString(5);
                mb.WarrantyTime = dr.GetInt32(6);
                mb.State = dr.GetString(7);
                mb.ImageID = dr.GetInt32(8);
                mb.Count = dr.GetInt32(9);
                mbname.Add(mb);
            }
            dr.Close();
            con.Close();
            return mbname;
        }
        #endregion

        #region[Mobile_SelectByID]
        public DTO.MobileDTO Mobile_SelectByID(int id)
        {
            DTO.MobileDTO mb = new DTO.MobileDTO();
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectMobileByID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@mobileid", id);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                mb.MobileID = dr.GetInt32(0);
                mb.Name = dr.GetString(1);
                mb.Brand = dr.GetString(2);
                mb.Description = dr.GetString(3);
                mb.Prize = dr.GetInt32(4);
                mb.Color = dr.GetString(5);
                mb.WarrantyTime = dr.GetInt32(6);
                mb.State = dr.GetString(7);
                mb.ImageID = dr.GetInt32(8);
                mb.Count = dr.GetInt32(9);
            }
            dr.Close();
            con.Close();
            return mb;
        }
        #endregion

        #region[Mobile_SelectByBrand]
        public List<DTO.MobileDTO> Mobile_SelectByBrand(string brand)
        {
            List<DTO.MobileDTO> mbbrand = new List<DTO.MobileDTO>();
            
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("SelectMobileByBrand", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@brand", brand);
            con.Open();
            MySqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                DTO.MobileDTO mb = new DTO.MobileDTO();
                mb.MobileID = dr.GetInt32(0);
                mb.Name = dr.GetString(1);
                mb.Brand = dr.GetString(2);
                mb.Description = dr.GetString(3);
                mb.Prize = dr.GetInt32(4);
                mb.Color = dr.GetString(5);
                mb.WarrantyTime = dr.GetInt32(6);
                mb.State = dr.GetString(7);
                mb.ImageID = dr.GetInt32(8);
                mb.Count = dr.GetInt32(9);
                mbbrand.Add(mb);
            }
            dr.Close();
            con.Close();
            return mbbrand;
        }
        #endregion

        #region [Mobile_Insert]
        public bool Mobile_Insert(DTO.MobileDTO mb)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("AddMobile", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@name", mb.Name));
            cmd.Parameters.Add(new MySqlParameter("@brand", mb.Brand));
            cmd.Parameters.Add(new MySqlParameter("@description", mb.Description));
            cmd.Parameters.Add(new MySqlParameter("@prize", mb.Prize));
            cmd.Parameters.Add(new MySqlParameter("@color", mb.Color));
            cmd.Parameters.Add(new MySqlParameter("@warrantytime", mb.WarrantyTime));
            cmd.Parameters.Add(new MySqlParameter("@state", mb.State));
            cmd.Parameters.Add(new MySqlParameter("@imageid", mb.ImageID));
            cmd.Parameters.Add(new MySqlParameter("@count", mb.Count));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

        #region [Mobile_Insert_1]
        public bool Mobile_Insert_1(int mobileid, int numberadd)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("InsertMobile", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@numberadd", numberadd));
            cmd.Parameters.Add(new MySqlParameter("@mobileid", mobileid));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

        #region [Mobile_Update]
        public bool Mobile_Update(DTO.MobileDTO mb)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("UpdateMobile", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@mobileid", mb.MobileID));
            cmd.Parameters.Add(new MySqlParameter("@name", mb.Name));
            cmd.Parameters.Add(new MySqlParameter("@brand", mb.Brand));
            cmd.Parameters.Add(new MySqlParameter("@description", mb.Description));
            cmd.Parameters.Add(new MySqlParameter("@prize", mb.Prize));
            cmd.Parameters.Add(new MySqlParameter("@color", mb.Color));
            cmd.Parameters.Add(new MySqlParameter("@warrantytime", mb.WarrantyTime));
            cmd.Parameters.Add(new MySqlParameter("@state", mb.State));
            cmd.Parameters.Add(new MySqlParameter("@imageid", mb.ImageID));
            cmd.Parameters.Add(new MySqlParameter("@count", mb.Count));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

        #region [Mobile_Delete]
        public bool Mobile_DeleteByID(DTO.MobileDTO mb)
        {
            Connection connecttion = new Connection();
            MySqlConnection con = new MySqlConnection(connecttion.Connect);
            MySqlCommand cmd = new MySqlCommand("DeleteMobileByID", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.Add(new MySqlParameter("@mobileid", mb.MobileID));

            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            return true;
        }
        #endregion

    }
}
